package org.acmwustl.bubble.logic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import org.acmwustl.bubble.util.UnixCrypt;

/**
 *
 * @author officer
 */
public class ConnectionManager {

    private static ConnectionManager instance;

    private Connection myCon;

    private PreparedStatement findMemberByCardStatement, updateBalanceStatement,
            isOfficerStatement, makeNewMemberStatement, existCheckStatement;

    public static ConnectionManager getInstance() {
        if(instance == null)
            instance = new ConnectionManager();
        return instance;
    }

    private ConnectionManager() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            establishConnection();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ConnectionManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void establishConnection() {
        try {
            if (myCon != null) {
                myCon.close();
                myCon = null;
            }
            String dbname = "mailing",
                   username = "username",
                   password = "xxxxxxxx";
            myCon = DriverManager.getConnection("jdbc:mysql://acm.wustl.edu/"+dbname, username, password);
            
            findMemberByCardStatement = myCon.prepareStatement("SELECT * FROM "+dbname+" WHERE card_id=?");
            updateBalanceStatement = myCon.prepareCall("UPDATE "+dbname+" SET balance=? WHERE card_id =?");
            isOfficerStatement = myCon.prepareStatement("SELECT * FROM "+dbname+" WHERE card_id=?");
            existCheckStatement = myCon.prepareStatement("SELECT * FROM "+dbname+" WHERE uname=? AND domain=?");
            makeNewMemberStatement = myCon.prepareStatement("INSERT INTO "+dbname+" (first_name, last_name," +
                    "uname, domain, email, send_email, is_national, has_paid," +
                    "card_id, balance, password, is_officer) VALUES (?,?,?,?,?,?,?," +
                    "?,?,?,?,?)");
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Database Error. Consult stdout.");
            ex.printStackTrace();
        }
    }

    public void makeNewMember(String fName, String lName,
            String uName, String domain, String email,
            String password, String cardID) {
        try {
            cardID = cardID.substring(9, cardID.length() - 1);
            cardID = encrypt(cardID);
            makeNewMemberStatement.setString(1, fName);
            makeNewMemberStatement.setString(2, lName);
            makeNewMemberStatement.setString(3, uName);
            makeNewMemberStatement.setString(4, domain);
            makeNewMemberStatement.setString(5, email);
            makeNewMemberStatement.setBoolean(6, true);
            makeNewMemberStatement.setBoolean(7, false);
            makeNewMemberStatement.setBoolean(8, true);
            makeNewMemberStatement.setString(9, cardID);
            makeNewMemberStatement.setFloat(10, 0.0f);
            makeNewMemberStatement.setString(11, UnixCrypt.crypt(password));
            makeNewMemberStatement.setBoolean(12, false);
            makeNewMemberStatement.executeUpdate();
            makeNewMemberStatement.clearParameters();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Database Error. Consult stdout.");
            ex.printStackTrace(System.err);
        }
    }

    public boolean existsUserName(String uName, String domain) {
        try {
            existCheckStatement.setString(1, uName);
            existCheckStatement.setString(2, domain);
            ResultSet rs = existCheckStatement.executeQuery();
            existCheckStatement.clearParameters();

            return rs.next();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Database Error. Consult stdout.");
            ex.printStackTrace();
        }
        return false;
    }

    public boolean existsCardNumber(String cardNumber) {
        try {
            cardNumber = cardNumber.substring(9, cardNumber.length() - 1);
            cardNumber = encrypt(cardNumber);
            findMemberByCardStatement.setString(1, cardNumber);
            ResultSet rs = findMemberByCardStatement.executeQuery();
            findMemberByCardStatement.clearParameters();
            return rs.next();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Database Error. Consult stdout.");
            ex.printStackTrace();
        }
        return false;
    }

    public String getName(String cardNumber) {
        try {
            cardNumber = cardNumber.substring(9, cardNumber.length() - 1);
            cardNumber = encrypt(cardNumber);
            findMemberByCardStatement.setString(1, cardNumber);
            ResultSet rs = findMemberByCardStatement.executeQuery();
            findMemberByCardStatement.clearParameters();
            if(rs.next()) {
                return rs.getString("first_name") + " " + rs.getString("last_name");
            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Database Error. Consult stdout.");
            ex.printStackTrace();
        }
        return "NoName";
    }

    public void updateBalanceInCents(String cardNumber, int cents) {
        try {
            cardNumber = cardNumber.substring(9, cardNumber.length() - 1);
            cardNumber = encrypt(cardNumber);
            updateBalanceStatement.setFloat(1,(float)cents/100.0f);
            updateBalanceStatement.setString(2, cardNumber);
            updateBalanceStatement.executeUpdate();
            updateBalanceStatement.clearParameters();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Database Error. Consult stdout.");
            ex.printStackTrace();
        }
    }

    public int getBalanceInCents(String cardNumber) {
        try {
            cardNumber = cardNumber.substring(9, cardNumber.length() - 1);
            cardNumber = encrypt(cardNumber);
            findMemberByCardStatement.setString(1, cardNumber);
            ResultSet rs = findMemberByCardStatement.executeQuery();
            findMemberByCardStatement.clearParameters();
            if(rs.next()) {
                float f = rs.getFloat("balance");
                return (int) (f * 100);
            }
            else return 0;
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Database Error. Consult stdout.");
            ex.printStackTrace();
        }
        return 0;
    }

    public boolean isRegistered(String cardNumber) {
        try {
            cardNumber = cardNumber.substring(9, cardNumber.length() - 1);
            cardNumber = encrypt(cardNumber);
            findMemberByCardStatement.setString(1, cardNumber);
            ResultSet rs = findMemberByCardStatement.executeQuery();
            findMemberByCardStatement.clearParameters();
            return rs.next();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Database Error. Consult stdout.");
            ex.printStackTrace();
        }
        return false;
    }

    public boolean isOfficer(String cardNumber) {
        try {
            cardNumber = cardNumber.substring(9, cardNumber.length() - 1);
            cardNumber = encrypt(cardNumber);
            isOfficerStatement.setString(1, cardNumber);
            ResultSet rs = isOfficerStatement.executeQuery();
            isOfficerStatement.clearParameters();
            if(rs.next()) {
                return rs.getBoolean("is_officer");
            }
            else return false;
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Database Error. Consult stdout.");
            ex.printStackTrace();
        }
        return false;
    }

    public String encrypt(String s) {
        int a = Integer.valueOf(s.substring(0,3));
        int b = Integer.valueOf(s.substring(3,6));
        int c = Integer.valueOf(s.substring(6,9));

        a = encryptHelper(a);
        b = encryptHelper(b);
        c = encryptHelper(c);
       
        return String.format("%05d%05d%05d",a,b,c);
    }

    public int encryptHelper(int x) {
        int answer = x;
        int n = 13837;
        int e = 3;
        if(e%2 == 0)
            answer = 1;

        int temp = (x*x)%n;
        for(int i=0; i<e/2; i++) {
            answer = (temp*answer) % n;
        }

        return answer;
    }
}
